﻿using MyFileView.Core;
using MyFileView.Shard;

using System;
using System.Data;
using System.Data.SQLite;
using System.Web.Hosting;

namespace MyFleView.Service
{
    public interface IDbService : RegisterAutofac
    {
        void connectToDatabase();
        void createTable();
        int fillTable(UploadFileModel uploadFile);
        DataTable SelectTable();
    }
    public class DbService: IDbService
    {
        SQLiteConnection m_dbConnection;
        SQLiteCommand command;
        public DbService()
        {
            connectToDatabase();
            createTable();


        }

        //创建一个连接到指定数据库
        public void connectToDatabase()
        {
            var path = HostingEnvironment.MapPath("~");
            //  SQLiteConnection.CreateFile("E:\\MyProject\\MyProject\\MyFleView\\MyFleView\\FileDatabase.db");
            m_dbConnection = new SQLiteConnection(@"Data Source="+ path + "FileDatabase.db;Version=3;");//没有数据库则自动创建
            m_dbConnection.Open();

        }

        //在指定数据库中创建一个table
        public void createTable()
        {
            string sql = "create table  if not exists FileView_File (Id NVARCHAR(100),fileName NVARCHAR(20),fileSizeByte bigint, suffix NVARCHAR(20),contentType NVARCHAR(300),filePath NVARCHAR(500))";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            close();
        }

        //插入一些数据
        public int fillTable(UploadFileModel uploadFile)
        {
            connectToDatabase();
            string sql = "insert into FileView_File (Id,fileName,fileSizeByte, suffix,contentType,filePath) values ('" + uploadFile.Id + "','" + uploadFile.FileName + "', "+ uploadFile.FileSizeByte + ",'" + uploadFile.FileSuffix + "','" + uploadFile.ContentType + "','" + uploadFile.FilePath + "')";
            command = new SQLiteCommand(sql, m_dbConnection);
            int res = command.ExecuteNonQuery();
            close();
            return res;
        }
        public DataTable SelectTable()
        {
            string sql = "select * from FileView_File";
            DataSet ds = new DataSet();
            SQLiteDataAdapter command = new SQLiteDataAdapter(sql, m_dbConnection);
            command.Fill(ds, "ds");
            close();
            return ds.Tables[0];


        }
        public void close()
        {
            m_dbConnection.Close();
            command.Dispose();
        }

        //使用sql查询语句，并显示结果
        void printHighscores()
        {
            string sql = "select * from highscores order by score desc";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
            Console.ReadLine();
        }
    }
}